import io
from urllib.parse import quote

import xlwt
import json
from django.db.models import Count, Sum
from django.http import JsonResponse, HttpResponse, StreamingHttpResponse
from django.shortcuts import render
from django.views.decorators.csrf import csrf_exempt
from app01.models import ReceiveList
from django.core.paginator import Paginator
from django.utils.safestring import mark_safe
from app01.utils.fieldshtml import fields_to_html, fields_to_save, yearmonth_to_html


def receive_list(request):
    condtions = {}
    jsyf = request.GET.get('jsyf', '')  # 结算月份
    if jsyf != '':
        condtions['结算月份__istartswith'] = jsyf

    wllx = request.GET.get('wllx', '')  # 物料类型
    if wllx != '':
        condtions['物料类型'] = wllx

    fylb = request.GET.get('fylb', '')  # 费用类型
    if fylb != '':
        condtions['费用类别'] = fylb

    gwh = request.GET.get('gwh', 'BGGYJH')  # 岗位号
    if gwh != '':
        condtions['申请人岗号__istartswith'] = gwh

    wldm = request.GET.get('wldm', '')  # 物料代码
    if wldm != '':
        condtions['物料代码'] = wldm

    xmh = request.GET.get('xmh', '')  # 项目号
    if xmh != '':
        condtions['项目号__icontains'] = xmh

    # 依据条件查询记录
    result_list = ReceiveList.objects.filter(**condtions).order_by('-结算月份', '申请人岗号')
    # 计算全部数据的合计数
    sum_all = result_list.aggregate(Count('物料短描述'), Sum('实发数量'), Sum('实发领用金额'))
    # print(sum_all)
    # 生成是否表格字段选择框HTML代码“select_str”，是否显示字段的配置“dicts”
    select_str, dicts = fields_to_html(request.session['info']['employeeid'], ReceiveList)

    # 生成分布HTML代码
    # page = Pagination(request, result_list)

    pageA = request.GET.get('page', 1)  # 获取指定页面
    pagesize = request.GET.get('pagesize', 10)  # 每页有多少条数据
    paginator = Paginator(result_list, pagesize)
    page_1 = paginator.get_page(pageA)  # 获取指定页面数据
    # print(page_1.object_list)

    # 生成表格底部的合计栏
    # templist = result_list[page.start_recno:page.end_recno]
    # sum_row = templist.aggregate(Count('物料短描述'), Sum('实发数量'), Sum('实发领用金额'))
    # sum_row = page_1.object_list.aggregate(Count('物料短描述'), Sum('实发数量'), Sum('实发领用金额'))

    # 计算当前页数据合计数
    sum_row = {}
    sum_c = 0
    sum_sl = 0
    sum_je = 0
    for row in page_1.object_list:
        # print(row.实发数量, row.实发领用金额)
        sum_c += 1
        sum_sl += row.实发数量
        sum_je += row.实发领用金额
    sum_row['物料短描述__count'] = sum_c
    sum_row['实发数量__sum'] = '{:,}'.format(sum_sl)
    sum_row['实发领用金额__sum'] = '{:,}'.format(sum_je)

    # 将数字转换成千分位表示
    for key, value in sum_all.items():
        if value is None:
            sum_all[key] = 0
        else:
            sum_all[key] = '{:,}'.format(value)
        # print(key, value)

    context = {
        'sum_row': sum_row,  # 页脚合计数
        'sum_all': sum_all,  # 全部数据合计数
        'fields': dicts,  # 是否显示列
        'select': select_str,  # 选择显示哪些列
        'active7': 'active',  # 被选择TAB页面标记，方便突出显示
        'title': '新品领用查询',
        'jsyf': jsyf,  # 过滤条件，下同
        'wllx': wllx,
        'fylb': fylb,
        'gwh': gwh,
        'wldm': wldm,
        'xmh': xmh,
        'select_jsyf': select_jsyf(jsyf),  # 筛选条件下拉数据
        'select_wllx': select_wllx(wllx),  # 筛选条件下拉数据
        'select_fylb': select_fylb(fylb),  # 筛选条件下拉数据
        'page_1': page_1,  # 要显示的页面数据
    }
    # print(locals())
    return render(request, 'receive_list.html', context)


def receive_group_list(request):
    condtions = {}
    jsyf = request.GET.get('jsyf', '')  # 结算月份
    if jsyf != '':
        condtions['结算月份__istartswith'] = jsyf

    sqrgh = request.GET.get('sqrgh', 'BGGYJH')  # 申请人岗号
    if sqrgh != '':
        condtions['申请人岗号__istartswith'] = sqrgh

    sqrxm = request.GET.get('sqrxm', '')  # 申请人姓名
    if sqrxm != '':
        condtions['申请人姓名'] = sqrxm

    wllx = request.GET.get('wllx', '')  # 物料类型
    if wllx != '':
        condtions['物料类型'] = wllx

    fylb = request.GET.get('fylb', '')  # 费用类型.order_by('-结算月份', '申请人岗号')
    if fylb != '':
        condtions['费用类别'] = fylb

    # 依据条件查询记录
    result_list = ReceiveList.objects.filter(**condtions).values('申请人岗号','申请人姓名').annotate(金额 = Sum('实发领用金额')).order_by('申请人岗号')
    # print(result_list)

    # 计算全部数据的合计数
    sum_all = result_list.aggregate(Count('申请人姓名'), Sum('金额'))
    # print(sum_all)

    # 生成分布HTML代码
    pageA = request.GET.get('page', 1)  # 获取指定页面
    pagesize = request.GET.get('pagesize', 10)  # 每页有多少条数据
    paginator = Paginator(result_list, pagesize)
    page_1 = paginator.get_page(pageA)  # 获取指定页面数据

    # 计算当前页数据合计数
    sum_row = {}
    sum_c = 0
    sum_je = 0
    for row in page_1.object_list:
        # print(row.实发数量, row.实发领用金额)
        sum_c += 1
        sum_je += row['金额']
    sum_row['申请人姓名__count'] = sum_c
    sum_row['金额__sum'] = '{:,}'.format(sum_je)

    # 将数字转换成千分位表示
    for key, value in sum_all.items():
        sum_all[key] = '{:,}'.format(value)

    context = {
        'sum_row': sum_row,  # 页脚合计数
        'sum_all': sum_all,  # 全部数据合计数
        'active6': 'active',  # 被选择TAB页面标记，方便突出显示
        'title': '点检员物料费查询',
        'jsyf': jsyf,  # 过滤条件，下同
        'wllx': wllx,
        'fylb': fylb,
        'sqrgh': sqrgh,
        'sqrxm': sqrxm,
        'select_jsyf': select_jsyf(jsyf),  # 筛选条件下拉数据
        'select_wllx': select_wllx(wllx),  # 筛选条件下拉数据
        'select_fylb': select_fylb(fylb),  # 筛选条件下拉数据
        'page_1': page_1,  # 要显示的页面数据
    }
    # print(locals())
    return render(request, 'receive_group_list.html', context)


def receive_fields(request):
    fields_str = request.GET.get('f')
    sel_fields = fields_str.split(',')
    # print(type(sel_fields))
    fields_to_save(request.session['info']['employeeid'], ReceiveList, sel_fields)
    return JsonResponse({'status': True})


def select_jsyf(jsyf):
    rows = ReceiveList.objects.values('结算月份').distinct().order_by('-结算月份')
    select_str_list = ['<select id="jsyf" name="jsyf" class="form-control">']
    select_str_list.append('<option value=""></option>')
    js_year = ''
    for v in rows:
        if v['结算月份'][:4] != js_year:
            js_year = v['结算月份'][:4]
            if js_year == jsyf:
                select_str_list.append(
                    '<option value="{}" selected="selected">{}</option>'.format(js_year, js_year))
            else:
                select_str_list.append('<option value="{}">{}</option>'.format(js_year, js_year))

        if v['结算月份'] == jsyf:
            select_str_list.append(
                '<option value="{}" selected="selected">{}</option>'.format(v['结算月份'], v['结算月份']))
        else:
            select_str_list.append('<option value="{}">{}</option>'.format(v['结算月份'], v['结算月份']))
    select_str_list.append('</select>')

    select_str = ''.join(select_str_list)
    return mark_safe(select_str)


def select_wllx(wllx):
    rows = ReceiveList.objects.values('物料类型').distinct().order_by('物料类型')
    select_str_list = ['<select id="wllx" name="wllx" class="form-control">']
    select_str_list.append('<option value=""></option>')
    for v in rows:
        if v['物料类型'] == wllx:
            select_str_list.append(
                '<option value="{}" selected="selected">{}</option>'.format(v['物料类型'], v['物料类型']))
        else:
            select_str_list.append('<option value="{}">{}</option>'.format(v['物料类型'], v['物料类型']))
    select_str_list.append('</select>')

    select_str = ''.join(select_str_list)
    return mark_safe(select_str)


def select_fylb(fylb):
    rows = ReceiveList.objects.values('费用类别').distinct().order_by('费用类别')
    select_str_list = ['<select id="fylb" name="fylb" class="form-control">']
    select_str_list.append('<option value=""></option>')
    for v in rows:
        if v['费用类别'] == fylb:
            select_str_list.append(
                '<option value="{}" selected="selected">{}</option>'.format(v['费用类别'], v['费用类别']))
        else:
            select_str_list.append('<option value="{}">{}</option>'.format(v['费用类别'], v['费用类别']))
    select_str_list.append('</select>')

    select_str = ''.join(select_str_list)
    return mark_safe(select_str)


def export_excel(request):
    condtions = {}
    jsyf = request.GET.get('jsyf', '')  # 结算月份
    if jsyf != '':
        condtions['结算月份__istartswith'] = jsyf

    wllx = request.GET.get('wllx', '')  # 物料类型
    if wllx != '':
        condtions['物料类型'] = wllx

    fylb = request.GET.get('fylb', '')  # 费用类型
    if fylb != '':
        condtions['费用类别'] = fylb

    gwh = request.GET.get('gwh', 'BGGYJH')  # 岗位号
    if gwh != '':
        condtions['申请人岗号__istartswith'] = gwh

    wldm = request.GET.get('wldm', '')  # 物料代码
    if wldm != '':
        condtions['物料代码'] = wldm

    xmh = request.GET.get('xmh', '')  # 项目号
    if xmh != '':
        condtions['项目号__icontains'] = xmh

    # 依据条件查询记录
    data_list = ReceiveList.objects.filter(**condtions).order_by('-结算月份', '申请人岗号')

    filename = quote('物料领用明细.xls')
    response = HttpResponse(content_type='application/vnd.msexecl')
    response['Content-Disposition'] = f'attachment; filename="{filename}"'

    fields = list(data_list.values()[0])

    # 创建工作簿
    xls_file = xlwt.Workbook(encoding='utf8')
    # 添加工作表，同一单元格可重复修改
    sheet = xls_file.add_sheet('Sheet1', cell_overwrite_ok=True)
    for col, title in enumerate(fields):
        sheet.write(0, col, data_list.model._meta.get_field(title).verbose_name)

    row_index = 1
    for row in data_list.values():
        # print(row)
        col_index = 0
        for value in row.values():
            sheet.write(row_index, col_index, value)
            col_index += 1
        row_index += 1
    xls_file.save(response)
    return response

def receive_table_list(request):
    # if request.method == 'POST':
        body = json.loads(request.body)
        table = getattr(models, body['table_name'])
        size, current = body['size'], body['current']
        total = table.objects.count()
        infos = table.objects.all().order_by()
        # Paginator用于后端分页
        paginator = Paginator(infos, per_page=size)
        page_object = paginator.page(current)
        total = infos.count()
        rows = []
        for item in page_object:
            d = model_to_dict(item)
            for k, v in d.items():
                if 'date' in k:
                    d[k] = str(v)
                elif k == "a_id":
                    d[k] = item.a_id.a_name
            rows.append(d)
        data = {'total': total, 'rows': rows}
        return HttpResponse(json.dumps(data), content_type='application/json')
